
////////////////////////////////////////////////////////////////////////////////////////////
// 文件名: SysTBViewDAL.cs
//
// 模块名称: SysTBView
//
// 作者: yqf
//
// 历史记录（记录修改记录，修改文件后请添加修改记录，注明修改时间、修改人，修改内容）:
// 2013-01-24    yqf    创建文件
////////////////////////////////////////////////////////////////////////////////////////////

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using HIDB;
using Model;
using Common;
using System.Text.RegularExpressions;
using MySql.Data.MySqlClient;
using System.Configuration;
using Npgsql;
using Oracle.ManagedDataAccess.Client;

namespace DAL
{
    /// <summary>
    /// SysTBView表数据访问层
    /// </summary>
    public partial class SysPublicDAL : DB
    {
        public SysPublicDAL(LoginInfo info)
        {
            base.loginInfo = info;
        }

        public List<SysNameValue> GetTableInfoList(string table)
        {
            //创建数据库对象
            HIDB.dbo con = base.conn();
            List<SysNameValue> list = new List<SysNameValue>();
            DataTable dt = new DataTable();
            IDataParameter[] paras = new SqlParameter[1];
            paras[0] = new SqlParameter("@TableName", table);
            dt = con.StoredDataTable("Working_db.Up_GetTableInfo", paras);
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    SysNameValue model = new SysNameValue();
                    model.Name = row["name"].ToString();
                    model.Value = row["value"].ToString();

                    list.Add(model);
                }
            }
            return list;
        }

        public DataTable GetTableInfoListMDP(string table)
        {
            //创建数据库对象
            HIDB.dbo con = base.conn();
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TableName", table) };
            return con.StoredDataTable("Working_db.Up_GetTableInfo_MDP", paras);
        }

        public string TestConeciton(int dbType, string connectionString)
        {
            string result = string.Empty;
            try
            {
                switch (dbType)
                {
                    case 1:
                        SqlConnection conn = new SqlConnection(connectionString);
                        conn.Open();
                        result = "1";
                        break;
                    case 2:
                        OracleConnection conn1 = new OracleConnection(connectionString);
                        conn1.Open();
                        result = "1";
                        break;
                    case 4:
                        MySqlConnection myConn = new MySqlConnection(connectionString);
                        myConn.Open();
                        result = "1";
                        break;
                    case 8:
                        NpgsqlConnection NpgsqlConn = new NpgsqlConnection(connectionString);
                        NpgsqlConn.Open();
                        result = "1";
                        break;
                }
            }
            catch (Exception ex)
            {
                result = ex.Message;
            }
            return result;
        }

        public string[] GetTypeDesc(string typeName)
        {
            string[] result = new string[4];
            try
            {
                string type = string.Empty;
                int length = 0;
                int precision = 0;
                int scale = 0;

                string regexString1 = @"^[A-Za-z0-9]+\(+[0-9]+\,+[0-9]+\)+$";//验证例如:decimal(18,0)类型
                string regexString2 = @"^[A-Za-z0-9]+\(+-?\d+\)+$";//验证例如:nvarchar(50)类型
                string regexString3 = @"^[A-Za-z0-9]+$";//验证例如:int类型
                typeName = typeName.ToLower().Replace("max", "-1");
                if (Regex.IsMatch(typeName, regexString1))
                {
                    typeName = typeName.Replace("(", "|").Replace(",", "|").Replace(")", "");
                    string[] typeArray = typeName.Split('|');
                    type = typeArray[0];
                    length = int.Parse(typeArray[1]);
                    precision = int.Parse(typeArray[1]);
                    scale = int.Parse(typeArray[2]);
                }
                else if (Regex.IsMatch(typeName, regexString2))
                {
                    typeName = typeName.Replace("(", "|").Replace(")", "");
                    string[] typeArray = typeName.Split('|');
                    type = typeArray[0];
                    length = int.Parse(typeArray[1]);
                }
                else if (Regex.IsMatch(typeName, regexString3))
                {
                    type = typeName;
                }
                else
                {
                    //类型错误
                }
                result[0] = type;
                result[1] = length.ToString();
                result[2] = precision.ToString();
                result[3] = scale.ToString();
            }
            catch (Exception)
            {

                throw;
            }
            return result;
        }

        public bool CheckType(string typeName, string columnName)
        {
            bool result = true;
            try
            {
                string type = string.Empty;
                int length = 0;
                int precision = 0;
                int scale = 0;

                string regexString1 = @"^[A-Za-z0-9]+\(+[0-9]+\,+[0-9]+\)+$";//验证例如:decimal(18,0)类型
                string regexString2 = @"^[A-Za-z0-9]+\(+-?\d+\)+$";//验证例如:nvarchar(50)类型
                string regexString3 = @"^[A-Za-z0-9]+$";//验证例如:int类型

                typeName = typeName.ToLower().Replace("max", "-1");
                if (Regex.IsMatch(typeName, regexString1))
                {
                    typeName = typeName.Replace("(", "|").Replace(",", "|").Replace(")", "");
                    string[] typeArray = typeName.Split('|');
                    type = typeArray[0];
                    length = int.Parse(typeArray[1]);
                    precision = int.Parse(typeArray[1]);
                    scale = int.Parse(typeArray[2]);
                }
                else if (Regex.IsMatch(typeName, regexString2))
                {
                    typeName = typeName.Replace("(", "|").Replace(")", "");
                    string[] typeArray = typeName.Split('|');
                    type = typeArray[0];
                    length = int.Parse(typeArray[1]);
                }
                else if (Regex.IsMatch(typeName, regexString3))
                {
                    type = typeName;
                }
                else
                {
                    return false;
                }

                DataTable dt = new DataTable();
                DataColumn dc0 = new DataColumn("ColumnID");
                DataColumn dc1 = new DataColumn("ColumnName");
                DataColumn dc2 = new DataColumn("ColumnType");
                DataColumn dc3 = new DataColumn("columnLength");
                DataColumn dc4 = new DataColumn("columnPresicion");
                DataColumn dc5 = new DataColumn("columnScale");
                dt.Columns.Add(dc0);
                dt.Columns.Add(dc1);
                dt.Columns.Add(dc2);
                dt.Columns.Add(dc3);
                dt.Columns.Add(dc4);
                dt.Columns.Add(dc5);
                DataRow dr = dt.NewRow();
                dr[0] = 1;
                dr[1] = columnName;
                dr[2] = type;
                dr[3] = length;
                dr[4] = precision;
                dr[5] = scale;
                dt.Rows.Add(dr);
                HIDB.dbo con = base.conn();
                DataTable dtResult = con.StoredDataTable("[Working_db].[CheckColumnType]", new SqlParameter[] { new SqlParameter("@t", dt) });
                if (dtResult != null && dtResult.Rows.Count > 0)
                {
                    result = false;
                }
            }
            catch (Exception)
            {
                result = false;
                //throw;
            }
            return result;
        }
        public string CheckType(DataTable dt)
        {
            string result = "1";
            try
            {

                HIDB.dbo con = base.conn();
                con.SqlTimeOut = 180;
                DataTable dtResult = con.StoredDataTable("[Working_db].[CheckColumnType]", new SqlParameter[] { new SqlParameter("@t", dt) });
                List<string> res = new List<string>(); 
                if (dtResult != null && dtResult.Rows.Count > 0)
                {
                    foreach (DataRow dr in dtResult.Rows)
                    {
                        res.Add(string.Format("字段: {0} 的字段类型 {1} 格式不正确，请检查!", dr[1], dr[2]));
                    }
                }
                if (res.Count > 0)
                    result = string.Join("<br />", res);
            }
            catch (Exception ex)
            {
                result = ex.Message; 
            }
            return result;
        }

        public DataTable GetDataTable(string sql)
        {
            try
            {
                HIDB.dbo con = base.conn();
                //string connStr = DBCon.GetConnectString();
                //string dataBase = ConfigurationManager.AppSettings["DataBase"].ToString();
                //HIDB.dbo con = HIDB.common.make(HIDB.DBType.SQLServer, connStr);

                return con.GetDataTable(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public bool DBJob(string jobStatus, string dbName)
        {
            SqlParameter[] para = new SqlParameter[] { 
                    new SqlParameter("@DBName",dbName),
                    new SqlParameter("@JobStatus",jobStatus)
                };
            try
            {
                HIDB.dbo DBCon = HIDB.common.make();
                DBCon.ExecuteSql("update [Working_db].[DBDeploy] set DBJobStatus=@JobStatus where DBName=@DBName", para);
                HIDB.dbo con = base.MSDB(); 
                con.ExecuteSql(@"
declare @jobName varchar(50)=@DBName+'_ETL'
if(@JobStatus='1')
EXEC dbo.sp_start_job @jobName
else
EXEC dbo.sp_stop_job @jobName
", para);

                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}